For StudentDetails, you will refer from the view: [dbo].[ vw_Homework_ALL] that is also located in the CCPP database. The following SELECT statement is what you will utilize in the OLE DB Source.
SELECT Distinct
StudentUserId
,[StudentName]
, StudentEmail
, (CASE WHEN 0 = CHARINDEX(' - ', [ClassName])
THEN NULL
ELSE SUBSTRING([ClassName],CHARINDEX('-', [ClassName])+1, LEN([ClassName]) )end) StartDate
,REPLACE(SUBSTRING([ClassName], CHARINDEX('(',[ClassName])+1, LEN([ClassName])), ')', '') AS Campus
FROM [dbo].[vw_Homework_ALL]
WHERE VideoID IS NOT NULL
AND StudentUserId IS NOT NULL
A Script Component will also be needed for the Data Flow of this DFT. The following GIF will provide further details. Please be aware that the GIF will instruct how the "CapitalName" column you created with the Script Component will be utilized in the Data Conversion and will map to the StudentName column for the OLE DB Destination.
* Add your code here
*/
}
/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
/// string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
/// Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
* Add your code here
*/
Row.CapitalName = InitCapString(Row.StudentName);
}
private string InitCapString(string p)
{
return new
System.Globalization.CultureInfo("en").TextInfo.ToTitleCase(p.ToLower());
throw new NotImplementedException();
}
}
FYI: Here are the mappings for the OLE DB Destination. There is no need to connect to StudentVideoId, LastModifiedBy, and LastDateModified, because they will automatically be populated thanks to the Identity Insert included in the SQL file you downloaded.
How the ODS database compares with the Staging Database